package com.course.dao;

import com.course.entity.Teacher;
import com.course.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDAO {

    // 根据用户名查找老师
    public Teacher findByUsername(String username) {
        String sql = "SELECT * FROM teachers WHERE username = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, username);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return extractTeacherFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 获取所有老师信息（不包括密码）
    public List<Teacher> getAllTeachers() {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT id, teacher_number, username, name, gender, department, email, phone FROM teachers";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                teachers.add(extractTeacherWithoutPasswordFromResultSet(rs));
            }
            System.out.println("TeacherDAO: Number of teachers retrieved: " + teachers.size()); // Debug print
            return teachers;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("TeacherDAO: Error retrieving teachers: " + e.getMessage()); // Debug print
            return teachers; // Return current list even on error
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 添加新老师
    public void addTeacher(Teacher teacher) {
        String sql = "INSERT INTO teachers (teacher_number, username, password, name, gender, department, email, phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, teacher.getTeacherNumber());
            pstmt.setString(2, teacher.getUsername());
            pstmt.setString(3, teacher.getPassword());
            pstmt.setString(4, teacher.getName());
            pstmt.setString(5, teacher.getGender());
            pstmt.setString(6, teacher.getDepartment());
            pstmt.setString(7, teacher.getEmail());
            pstmt.setString(8, teacher.getPhone());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 根据ID查找老师
    public Teacher findById(int id) {
        String sql = "SELECT * FROM teachers WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return extractTeacherFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 根据姓名查找老师
    public Teacher findByName(String name) {
        String sql = "SELECT * FROM teachers WHERE name = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, name);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return extractTeacherFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 更新老师信息
    public void updateTeacher(Teacher teacher) {
        String sql = "UPDATE teachers SET username=?, name=?, gender=?, department=?, email=?, phone=? WHERE id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, teacher.getUsername());
            pstmt.setString(2, teacher.getName());
            pstmt.setString(3, teacher.getGender());
            pstmt.setString(4, teacher.getDepartment());
            pstmt.setString(5, teacher.getEmail());
            pstmt.setString(6, teacher.getPhone());
            pstmt.setInt(7, teacher.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 更新教师密码
    public boolean updatePassword(int teacherId, String newPassword) {
        String sql = "UPDATE teachers SET password = ? WHERE id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newPassword);
            pstmt.setInt(2, teacherId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 从ResultSet中提取老师信息（包含密码，用于登录校验等）
    private Teacher extractTeacherFromResultSet(ResultSet rs) throws SQLException {
        Teacher teacher = new Teacher();
        teacher.setId(rs.getInt("id"));
        teacher.setTeacherNumber(rs.getString("teacher_number"));
        teacher.setUsername(rs.getString("username"));
        teacher.setPassword(rs.getString("password")); // 包含密码
        teacher.setName(rs.getString("name"));
        teacher.setGender(rs.getString("gender"));
        teacher.setDepartment(rs.getString("department"));
        teacher.setEmail(rs.getString("email"));
        teacher.setPhone(rs.getString("phone"));
        return teacher;
    }

    // 从ResultSet中提取老师信息（不包括密码，用于管理页面展示）
    private Teacher extractTeacherWithoutPasswordFromResultSet(ResultSet rs) throws SQLException {
        Teacher teacher = new Teacher();
        teacher.setId(rs.getInt("id"));
        teacher.setTeacherNumber(rs.getString("teacher_number"));
        teacher.setUsername(rs.getString("username"));
        // 不设置密码字段
        teacher.setName(rs.getString("name"));
        teacher.setGender(rs.getString("gender"));
        teacher.setDepartment(rs.getString("department"));
        teacher.setEmail(rs.getString("email"));
        teacher.setPhone(rs.getString("phone"));
        return teacher;
    }

    public List<Teacher> searchTeachers(String teacherNumber, String teacherName, String department) {
        List<Teacher> teachers = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBConnection.getConnection();
            StringBuilder sql = new StringBuilder("SELECT id, teacher_number, username, name, gender, department, email, phone FROM teachers WHERE 1=1");
            List<String> params = new ArrayList<>();

            if (teacherNumber != null && !teacherNumber.trim().isEmpty()) {
                sql.append(" AND teacher_number LIKE ?");
                params.add("%" + teacherNumber + "%");
            }
            if (teacherName != null && !teacherName.trim().isEmpty()) {
                sql.append(" AND name LIKE ?");
                params.add("%" + teacherName + "%");
            }
            if (department != null && !department.trim().isEmpty()) {
                sql.append(" AND department LIKE ?");
                params.add("%" + department + "%");
            }

            System.out.println("TeacherDAO: Search SQL: " + sql.toString());
            pstmt = conn.prepareStatement(sql.toString());

            for (int i = 0; i < params.size(); i++) {
                pstmt.setString(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();

            while (rs.next()) {
                teachers.add(extractTeacherWithoutPasswordFromResultSet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return teachers;
    }

    public Teacher findTeacherByTeacherNumberPasswordAndEmail(String teacherNumber, String password, String email) {
        String sql = "SELECT * FROM teachers WHERE teacher_number = ? AND password = ? AND email = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, teacherNumber);
            pstmt.setString(2, password);
            pstmt.setString(3, email);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return extractTeacherFromResultSet(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
} 